/*
 * Copyright (C) 2010 The Android Open Source Project 
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at 
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software 
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and 
 * limitations under the License.
 */

package org.lhlzi.ithink;

import java.io.File;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;

public class SQLLocalStorage {
	
	
	public final static String TAG = SQLLocalStorage.class.toString();
	
	/*
	 * mobile_number_library 表的基本字段
	 */
	public final static String PREFIX_COLUMN = "prefix";
	public final static String LOCATION_COLUMN = "location";
	public final static String TYPE_COLUMN = "type";
	public final static String AREACODE_COLUMN ="areacode";
	public final static String POSTCODE_COLUMN ="postcode";
	
	/*
	 * 表名称
	 */
	private final static String MNLIB_TABLE = "mobile_number_library";
	
	/*
	 * 创建表语句，如果表不存在
	 */
	private final static String MNLIB_TABLE_DDL = "CREATE TABLE IF NOT EXISTS mobile_number_library " +
			"(prefix int, location varchar(50), type varchar(50), areacode varchar(50), " +
			"postcode varchar(50), PRIMARY KEY(prefix))";
	
	/*
	 * 为字段创建索引(增强查询效率)，如果索引不存在
	 */
	private final static String MNLIB_INDEX_DDL = "CREATE INDEX IF NOT EXISTS IND_MNLIB ON mobile_number_library(" +
			"prefix)";
	
	/*
	 * 特殊号码的字段(表special_number_library内容)
	 */
	public final static String NUMBER_COLUMN = "number";
	public final static String HEADING_COLUMN = "heading";
	
	/*
	 * 表名称
	 */
	private final static String SNLIB_TABLE = "special_number_library";
	
	/*
	 * 创建表语句，如果表不存在
	 */
	private final static String SNLIB_TABLE_DDL ="CREATE TABLE IF NOT EXISTS special_number_library" +
			"(number varchar(30), heading varchar(50), PRIMARY KEY(number))"; 
	
	/*
	 * 创建索引
	 */
	private final static String SNLIB_INDEX_DDL = "CREATE INDEX IF NOT EXISTS IND_SNLIB on special_number_library(" +
			"number)";
	
	
	
	public static String SD_PATH = SQLLocalStorage.getSDCardPath();
	public static String STORAGE_PATH = SD_PATH + "/ithink/data/";
	
	public final static String SQLITE_DB = "mnlib.db";
	
	private final static String QUERY_FROM_MN = "SELECT * FROM mobile_number_library WHERE prefix=?";
	private final static String QUERY_FROM_SN = "SELECT * FROM special_number_library WHERE number=?";
	
	private static SQLiteDatabase db;
	
	private static SQLLocalStorage localStorage;
	
	private static boolean mObject = true;
	
	public static SQLLocalStorage getInstance() {
		
		createPath();
		
//		Log.i(TAG, "localStorage="+(localStorage == null ? "NULL" : "NOT NULL")
//				+ ",mObject=" + mObject
//				+ ",SD_PATH=" + SD_PATH);
		
		if (localStorage == null && mObject) {

			localStorage = new SQLLocalStorage();
			
			//Log.i(TAG, "new instance in SQLLocalStorage class.");
		}
		
		return localStorage;
	}
	
	public static String getSDCardPath() {
		String status = Environment.getExternalStorageState();
		
		if ( Environment.MEDIA_MOUNTED.equals(status)) {
			
			if (Environment.getExternalStorageDirectory().canWrite()) {
				return Environment.getExternalStorageDirectory().getPath();
			}
		}
		
		return null;
	}
	
	
	public static SQLLocalStorage resetLocalStorage() {
		if (db != null)
			db.close();
		localStorage = null;
		SD_PATH = getSDCardPath();
		STORAGE_PATH = SD_PATH + "/ithink/data/";
		
		return getInstance();
		
	}
	
	private static void createPath() {
		
		if (SD_PATH == null) { 
			mObject = false;
			
			return;
		}
		
		File f = new File(STORAGE_PATH);
		if (!f.exists()) f.mkdirs();
		
		mObject = true;
	}
	
	
	
	/**
	 * 内部构造函数，不被外部调用。用于类的初始化
	 */
	private SQLLocalStorage() {

		String sqlitePath = STORAGE_PATH + SQLITE_DB;
		db = SQLiteDatabase.openDatabase(sqlitePath, null, 
				SQLiteDatabase.CREATE_IF_NECESSARY);
		
		db.execSQL(SQLLocalStorage.MNLIB_TABLE_DDL);
		db.execSQL(SQLLocalStorage.MNLIB_INDEX_DDL);
		
		db.execSQL(SQLLocalStorage.SNLIB_TABLE_DDL);
		db.execSQL(SQLLocalStorage.SNLIB_INDEX_DDL);
	}
	
	/**
	 * 获取数据库记录，返回一个<code>Bundle</code>对象
	 * @param prefix 每个手机号码的7位前缀
	 * @return <code>Bundle</code>
	 */
	public Bundle get(int prefix) {
		String sql = SQLLocalStorage.QUERY_FROM_MN;
		return get(sql, String.valueOf(prefix));
	}
	
	/**
	 * 获取数据库记录，返回一个<code>Bundle</code>对象
	 * @param number 特殊号码
	 * @return <code>Bundle</code>对象
	 */
	public Bundle get(String number) {
		String sql = SQLLocalStorage.QUERY_FROM_SN;
		return get(sql, number);
	}
	
	
	private Bundle get(String query, String field) {
		
		
		
		String sql = query;
		
		Cursor c = db.rawQuery(sql, 
				new String[] {
					field,
				});
		
		Bundle extras = null;
		
		if (c.moveToFirst()) {
			extras = new Bundle();
			extras.putInt(PREFIX_COLUMN, Integer.parseInt(c.getString(0)));
			extras.putString(LOCATION_COLUMN, c.getString(1));
			extras.putString(TYPE_COLUMN, c.getString(2));
			extras.putString(AREACODE_COLUMN, c.getString(3));
			extras.putString(POSTCODE_COLUMN, c.getString(4));

		}
		c.close();
		return extras;
	}
	
	public void put(String number, String heading) {
		
		ContentValues initialValues = new ContentValues();
		
		initialValues.put(SQLLocalStorage.NUMBER_COLUMN, number);
		initialValues.put(SQLLocalStorage.HEADING_COLUMN, heading);
		
		db.insert(SQLLocalStorage.SNLIB_TABLE, null, initialValues);
	}
	
	
	
	
}
